# -*- coding: utf-8 -*-
#
# Author: oldcrane
# Date: 2017-03-02
#
# export mysql db structure to html files. 
# index.html file is also generated to view all tables and link to cooresponding file. 
# all the files are saved in a folder with database name.
# - <dbname> (folder)
# |- index.html
# |- <table_name>.html
#
# This program is only tested on python v3.6.0

import argparse
import os
import glob
import datetime
import pymysql
from collections import OrderedDict

# 读取文件
def read_file(path, charset='utf-8'):
    with open(path, "r", encoding=charset) as f:
        return f.read()

        
# 写文件
def write_file(path, content, charset='utf-8'):
    with open(path, "w+", encoding='utf-8') as f:
        f.write(content)

        
# 把数据库表清单，输出到index.html
def export_index(db, tables, file_path):
    STR_HEADER = "<table>\n<tr><th>数据库表</th><th>字符集</th><th>备注</th><tr>\n"
    STR_FOOTER = "</table>\n"
    STR_TEMPLATE = "<tr><td><a href='%s.html'>%s</a></td><td>%s</td><td>%s</td></tr>\n"
    str = STR_HEADER;
    for name, charset, comment in tables:
        str = str + STR_TEMPLATE % (name, name, charset, comment)
    str = str + STR_FOOTER

    str_page = read_file("template_index.html")
    str_page = str_page.replace("{{db_name}}", db)
    str_page = str_page.replace("{{update_time}}", datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'))
    str_page = str_page.replace("{{table_num}}", '%d' % (len(tables)))
    str_page = str_page.replace("{{content}}", str)
    #print(str)
    write_file(file_path, str_page)


# 把一张表的数据结构，导出为html文件
# @param db string 数据库名
# @param table_name string 数据库表名
# @param charset string 数据库表用的字符集
# @param table_comment string 数据库表的注释
# @param columns list of tuple 所有字段的属性集合
# @param template string html文件模板字
# @param file_path string 指定的文件路径
# @return
def export_one_table(db, table_name, charset, table_comment, columns, indices, template, file_path):
    STR_HEADER = "<table>\n<tr><th>字段名</th><th>类型</th><th>必填</th><th>默认值</th><th>备注</th><tr>\n"
    STR_FOOTER = "</table>\n"
    ROW_TEMPLATE = "<tr><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td></tr>\n"
    str = STR_HEADER
    for name, default, nullable, type, comment, column_key, extra in columns:
        if column_key or extra:
            if comment:
                comment = '%s %s<br/>%s' % (column_key, extra, comment)
            else:
                comment = '%s %s' % (column_key, extra)
        mandatory = 'Y' if 'NO'==nullable else ''
        default = '' if None==default else default
        str = str + ROW_TEMPLATE % (name, type, mandatory, default, comment)
    str = str + STR_FOOTER
    
    str = str + "<br/>索引：<br/>\n"
    for idx_name, index in indices.items():
        str_unique = "unique" if index[1]==0 else ""
        str = str + "- " + idx_name + "(" + index[0] + ") " + str_unique + "<br/>\n"
    
    str_page = template.replace("{{db_name}}", db)
    str_page = str_page.replace("{{table_name}}", table_name)
    str_page = str_page.replace("{{charset}}", charset)
    str_page = str_page.replace("{{table_comment}}", table_comment)
    str_page = str_page.replace("{{content}}", str)
    write_file(file_path, str_page)

    
    
# 获取数据库结构，并导出为html文件
# @param host string 数据库服务主机名
# @param port string 数据库服务端口
# @param user string 数据库用户名
# @param password string 数据库密码
# @param db string 数据库名
# @param target 导出到哪个目录
# @return
def export_structure(host, port, user, password, db, target):
    # connect to db
    try:
        conn = pymysql.connect(
            host=host, port=port, user=user, passwd=password, db=db, charset='utf8')
    except Exception as e:
        print("Error " + str(e))
        return None

    cursor = conn.cursor()
    print("Database %s on %s:%s connected" % (db, host, port))

    # 创建目录，如果目录已经存在，删除目录中的所有html文件
    folder = os.path.join(target, db)
    folder_exists = os.path.exists(folder)
    if not folder_exists:
        os.makedirs(folder)
    else:
        for file in glob.glob(os.path.join(folder, "*.html")):
            os.remove(file)
    print("Target folder %s is initialized." % os.path.abspath(folder))
            
    # 列举数据库表，输出为index.html
    sql_tables = "select table_name, table_collation, table_comment from information_schema.tables where table_schema='%s' and table_type='base table'"
    # warning: 这样做参数拼接不安全，应该换成prepared statement
    cursor.execute(sql_tables % db)
    tables = cursor.fetchall()
    #print(tables)
    export_index(db, tables, os.path.join(folder, 'index.html'))
    print("File index.html is generated.")
    print("Generating html file for each table......")

    # 为每个数据库表生产一个html文件
    template_one_table = read_file("template_table.html")
    for name, charset, comments in tables:
        # 查询表结构
        sql_columns = "select column_name, column_default, is_nullable, column_type, column_comment, column_key, extra from information_schema.columns where table_schema='%s' and table_name='%s'"
        cursor.execute(sql_columns % (db, name))
        columns = cursor.fetchall()

        # 查询该表的索引
        sql_index = "SELECT index_name, column_name, non_unique FROM information_schema.statistics WHERE table_schema = '%s' AND table_name = '%s' order by index_name, seq_in_index"
        cursor.execute(sql_index % (db, name))
        index_details = cursor.fetchall()
        indices = OrderedDict()
        for idx_name, idx_column, idx_non_unique in index_details:
            if idx_name in indices:
                index = indices[idx_name]
                index[0] = index[0] + ', ' + idx_column
            else:
                index = [idx_column, idx_non_unique]
                indices[idx_name] = index
        export_one_table(db, name, charset, comments, columns, indices, template_one_table, os.path.join(folder, name+'.html'))
        #print('table %s exported' % name)

    print("HTML file for each table generated.")
        
    # 关闭数据连接
    cursor.close()
    conn.close()
    print("Database connection closed.")
    print('Structure of db is exported. Please visit index.html in %s folder.' % db)
    
def run():
    parser = argparse.ArgumentParser(description='Export mysql db structure to html files.')
    parser.add_argument('--host', default='localhost', help='host, default:localhost')
    parser.add_argument('--port', default=3306, type=int, help='port, default:3306')
    parser.add_argument('--user', default='root', help='username, default:root')
    parser.add_argument('--password', default='', help='password, default:(empty)')
    parser.add_argument('--target', default='../doc', help='output folder, default:(current path)')
    parser.add_argument('db', help='database name')
    args = parser.parse_args()
    # print(args)

    if not os.path.exists(args.target):
        print("Error: path(" + args.target +") not exists")
        return
        
    export_structure(args.host, args.port, args.user, args.password, args.db, args.target)


if __name__ == '__main__':
    run()
